﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using olierFrm.DataBase.FrameWork.Plugin;
using olierFrm.GeneralCache;
using System.Data.SqlClient;

namespace olierFrm.DataBase.FrameWork.Business
{
    public class BusinessSQL : DefinedSQL
    {
        public WF_Business Business
        {
            get;
            private set;
        }

        public List<WF_BusinessParams> BusinessParams
        {
            get;
            private set;
        }

        private List<WF_BusinessChecks> BusinessChecks { get; set; }
        private List<WF_BusinessProcs> BusinessProcs { get; set; }

        public const string BusinessCachePrefix = "Business=";
        public const string BusinessCheckCachePrefix = "BusinessCheck=";
        public const string BusinessParamCachePrefix = "BusinessParam=";
        public const string BusinessProcCachePrefix = "BusinessProc=";

        public BusinessSQL(string SqlID)
        {
            this.SqlID = SqlID;

            string CacheKey = BusinessCachePrefix + SqlID;
            if (!Cache.KeyExists(CacheKey))
            {
                var business = (from b in this.DBEntity.WF_Business where b.BusinessID == SqlID select b).ToList();
                if (!business.Any())
                    throw new FrameWorkError()
                    {
                        SqlID = this.SqlID,
                        ErrorObject = "Business",
                        ErrorType = "执行查询时",
                        Summary = "Business没有定义"
                    };
                Business = business.First();
                Cache.Add(CacheKey, Business);
            }
            else
                Business = (WF_Business)Cache.Get(CacheKey);


            string CacheCheckKey = BusinessCheckCachePrefix + SqlID;
            if (!Cache.KeyExists(CacheCheckKey))
            {
                BusinessChecks = (from b in this.DBEntity.WF_BusinessChecks where b.BusinessID == SqlID select b).ToList();

                Cache.Add(CacheCheckKey, BusinessChecks);

            }
            else
                BusinessChecks = (List<WF_BusinessChecks>)Cache.Get(CacheCheckKey);


            string CacheParamKey = BusinessParamCachePrefix + SqlID;
            if (!Cache.KeyExists(CacheParamKey))
            {
                BusinessParams = (from b in this.DBEntity.WF_BusinessParams where b.BusinessID == SqlID select b).ToList();

                Cache.Add(CacheParamKey, BusinessParams);
            }
            else
                BusinessParams = (List<WF_BusinessParams>)Cache.Get(CacheParamKey);
            //if (BusinessParams != null)
            foreach (var b in BusinessParams)
            {
                this.ObjectSQLParam.Add(new ObjectSQLParam()
                {
                    Idx = b.Idx,
                    ParamName = b.ParamName,
                    ParamType = b.ParamType,
                    Repeated = b.Repeated,
                    OutPut = b.Putout
                });
            }


            string CacheProcKey = BusinessProcCachePrefix + SqlID;
            if (!Cache.KeyExists(CacheProcKey))
            {
                BusinessProcs = (from b in this.DBEntity.WF_BusinessProcs where b.BusinessID == SqlID && b.Enabled orderby b.Idx select b).ToList();
                Cache.Add(CacheProcKey, BusinessProcs);
            }
            else
                BusinessProcs = (List<WF_BusinessProcs>)Cache.Get(CacheProcKey);
            //if (BusinessProcs != null)
            foreach (var b in BusinessProcs)
            {
                this.ObjectSQL.Add(new ObjectSQL()
                {
                    SqlID = b.BusinessID,
                    Idx = b.Idx,
                    SQLText = b.SQL,
                    Repeated = b.Repeated,
                    InterActive = b.InterActive,
                    AffectedParam = b.AffectedParam
                });
            }
        }

        public int ExecuteSQL()
        {
            if (!Check()) return 0;
            return ExecuteTransaction(ObjectSQL, ObjectSQLParam);

        }

        /// <summary>
        /// 计算参数中重复参数的最大个数
        /// </summary>
        /// <param name="RepeatedParams">重复参数</param>
        /// <returns></returns>
        private int RepeatedParamLength(List<ObjectSQLParam> RepeatedParams, string AffectedParamName)
        {
            var repeatedParam_length = 0;
            var repeatedParam = "";//debug
            //var repeatedParam= Params.Where(p => p.Repeated);
            foreach (var p in RepeatedParams)
            {
                if (p.ParamName == AffectedParamName) continue;
                object paramValue = null;
                var frs = RepeatedParams.Where(oo => oo.ParamName == p.ParamName);
                if (frs.Any())
                    paramValue = frs.First().ParamValue;
                if (paramValue == null) continue;

                int length = ((ArrayList)paramValue).Count;
                if (length > repeatedParam_length)
                {
                    repeatedParam_length = length;
                    repeatedParam = frs.First().ParamName;
                }
            }

            return repeatedParam_length;
        }

        private int ExecuteTransaction(List<ObjectSQL> SQLText, List<ObjectSQLParam> Param)
        {
            int rows = 0;
            SqlTransaction tran = SqlHelper.StaConnectoin.BeginTransaction();

            //dynamic Affected = new
            //{
            //    ParamName = "",
            //    ParamValue = "",
            //    ParamType = ""
            //};

            string AffectedParamName = "";
            //string AffectedParamValue = "";
            //string AffectedParamType = "";
            foreach (var s in SQLText)
            {
                if (s.SQLText.Trim().Length == 0) continue;
                //首先找出SQL中的所有参数
                var filterParams = SqlHelper.FilterParamsForSqlTextByObjectSQLParam(s.SQLText, Param);
                //if (filterParams == null) continue;

                //非重复
                if (!s.Repeated)
                {
                    var noRepeatedParam = filterParams.Where(p => p.Repeated);
                    if (noRepeatedParam.Any())
                        throw new Exception("在序号：" + s.Idx + "，SQL中含有重复参数“" + noRepeatedParam.First().ParamName + "”，是否没有在勾选重复SQL！");
                    //非交互模式
                    if (!s.InterActive)
                        rows += SqlHelper.ExecuteQuery(s.SQLText, filterParams, false, tran);
                    else
                    {
                        //找出交互模式的参数
                        var aps = Param.Where(fp => fp.ParamName == s.AffectedParam);
                        if (!aps.Any())
                        {
                            throw new Exception("在序号：" + s.Idx + "设置了交互模式，没有找到交互参数：" + s.AffectedParam);
                        }
                        var dt = SqlHelper.ExecuteQuery(s.SQLText, filterParams, "", false, 0, 0, tran);
                        if (dt.Rows.Count != 1)
                        {
                            throw new Exception("在序号：" + s.Idx + "设置了交互模式，返回行数必须为1行。");
                        }
                        var ap = aps.First();
                        var apv = dt.Rows[0][s.AffectedParam];
                        ap.ParamValue = apv;
                        AffectedParamName = s.AffectedParam;
                        //Affected.ParamValue = apv.ToString();
                        //Affected.ParamType = ap.ParamType;
                    }
                    continue;
                }

                //找出重复参数
                var rps = filterParams.Where(p => p.Repeated || p.ParamName == AffectedParamName);
                List<ObjectSQLParam> repeatedParam = new List<ObjectSQLParam>();
                if (rps.Any())
                {
                    repeatedParam = rps.ToList();
                }
                else
                {
                    throw new Exception("在重复SQL中没有配置重复参数！");
                }


                var repeatedParam_length = RepeatedParamLength(repeatedParam.ToList(), AffectedParamName);
                if (repeatedParam_length == 0) continue;
                var osp = (List<ObjectSQLParam>)filterParams.Clone();
                //var notRepeate = filterParams.Where(p => !p.Repeated).ToList();
                //if (notRepeate.Count != 0)
                //{
                //    osp.AddRange(notRepeate);
                //}
                //执行SQL是根据重复参数的最大个数为次数
                for (int i = 0; i < repeatedParam_length; i++)
                {
                    foreach (var o in repeatedParam)
                    {
                        //忽略一些空参数
                        try
                        {
                            var osps = osp.Where(oo => oo.ParamName == o.ParamName);
                            if (osps.Any())
                                osps.First().ParamValue = ((ArrayList)o.ParamValue)[i];
                        }
                        catch { }
                    }
                    try
                    {
                        rows += SqlHelper.ExecuteQuery(s.SQLText, osp, false, tran);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("在序号：" + s.Idx + "\r\n" + ex.Message);
                    }
                }
            }
            try
            {
                tran.Commit();
            }
            catch (Exception ex)
            {
                throw ex;

            }
            finally
            {
                this.Dispose();
            }
            return rows;
        }



        private bool Check()
        {
            if (BusinessChecks == null) return true;
            foreach (var b in BusinessChecks)
            {
                if (!b.Enabled) continue;

                if (b.ValidateType.ToUpper().Equals("REQUIRED"))
                {

                    var param = FindParamByName(b.ParamToValidate);
                    //if (param.ParamValue == null) continue;

                    if (param.Repeated)
                    {

                        ArrayList arrParamValue = (ArrayList)param.ParamValue;
                        for (int i = 0; i < arrParamValue.Count; i++)
                            if (arrParamValue[i].ToString().Equals(string.Empty))
                            {
                                throw new FrameWorkError()
                                {
                                    SqlID = this.SqlID,
                                    ErrorObject = "Business",
                                    ErrorType = "Business检查：REQUIRED",
                                    Summary = b.Summary
                                };
                            }
                    }
                    else
                    {
                        if (param.ParamValue.ToString().Equals(string.Empty))
                        {
                            throw new FrameWorkError()
                            {
                                SqlID = this.SqlID,
                                ErrorObject = "Business",
                                ErrorType = "Business检查：REQUIRED",
                                Summary = b.Summary

                            };
                        }
                    }

                }
                else if (b.ValidateType.ToUpper().Equals("QUERY"))
                {
                    var param = FindParamByName(b.ParamToValidate);
                    if (param.ParamValue == null) continue;
                    //ArrayList arrParamValue = new ArrayList();
                    if (param.Repeated)
                    {
                        //var filterParams = SqlHelper.FilterParamsForSqlTextByObjectSQLParam(b.SQL, ObjectSQLParam);
                        //var repeatedParams = filterParams.Where(p => p.Repeated);
                        //int repeatedParam_length = SqlHelper.RepeatedParamLength(repeatedParams.ToList());

                        var osp = (List<ObjectSQLParam>)ObjectSQLParam.Clone();
                        var repeatedParam = ObjectSQLParam.Where(o => o.Repeated).ToList();
                        var arrParamValue = (ArrayList)param.ParamValue;
                        for (int i = 0; i < arrParamValue.Count; i++)
                        {
                            foreach (var o in repeatedParam)
                            {
                                osp.Where(oo => oo.ParamName == o.ParamName).First().ParamValue = ((ArrayList)o.ParamValue)[i];
                            }
                            if (SqlHelper.ExecuteQuery(b.SQL, osp, "QUERY").Rows.Count == 0)
                            {
                                throw new FrameWorkError()
                                {
                                    SqlID = this.SqlID,
                                    ErrorObject = "Business",
                                    ErrorType = "Business检查：QUERY",
                                    Summary = b.Summary
                                };
                            }
                        }
                    }
                    else
                    {
                        if (SqlHelper.ExecuteQuery(b.SQL, ObjectSQLParam.Where(oo => !oo.Repeated).ToList(), "QUERY").Rows.Count == 0)
                        {
                            throw new FrameWorkError()
                            {
                                SqlID = this.SqlID,
                                Param = param.ParamName,
                                ErrorObject = "Business",
                                ErrorType = "Business检查：QUERY",
                                Summary = b.Summary
                            };
                        }
                    }


                }
                else if (b.ValidateType.ToUpper().Equals("SQL"))
                {
                    var param = FindParamByName(b.ParamToValidate);
                    if (param.ParamValue == null) continue;
                    if (param.Repeated)
                    {
                        var osp = (List<ObjectSQLParam>)ObjectSQLParam.Clone();
                        var osps = ObjectSQLParam.Where(o => o.Repeated).ToList();
                        var arrParamValue = (ArrayList)param.ParamValue;
                        for (int i = 0; i < arrParamValue.Count; i++)
                        {
                            foreach (var o in osps)
                            {
                                osp.Where(oo => oo.ParamName == o.ParamName).First().ParamValue = ((ArrayList)o.ParamValue)[i];
                            }
                            var sqlValidate = SqlHelper.ExecuteQuery(b.SQL, osp, "SQL");
                            if (sqlValidate.Rows.Count == 0 || sqlValidate.Rows[0]["ResultCode"].ToString() != "0")
                            {
                                throw new FrameWorkError()
                                {
                                    SqlID = this.SqlID,
                                    ErrorObject = "Business",
                                    ErrorType = "Business检查：SQL",
                                    Summary = b.Summary
                                };
                            }
                        }
                    }
                    else
                    {
                        if (SqlHelper.ExecuteQuery(b.SQL, ObjectSQLParam.Where(oo => !oo.Repeated).ToList(), "SQL").Rows[0]["ResultCode"].ToString() != "0")
                        {
                            throw new FrameWorkError()
                            {
                                SqlID = this.SqlID,
                                ErrorObject = "Business",
                                ErrorType = "Business检查：SQL",
                                Summary = b.Summary
                            };
                        }
                    }
                }
                else if (b.ValidateType.ToUpper().Equals("COMPARETO"))
                {
                    if (!FindParamByName(b.ParamToCompare).ParamValue.Equals(FindParamByName(b.ParamToValidate).ParamValue))
                    {
                        throw new FrameWorkError()
                        {
                            SqlID = this.SqlID,
                            ErrorObject = "Business",
                            ErrorType = "Business检查：COMPARETO",
                            Summary = b.Summary
                        };
                    }
                }
            }

            return true;
        }

        public void AddParam(string ParamName, object ParamValue)
        {
            var op = (from o in this.ObjectSQLParam where o.ParamName == ParamName select o).ToList();
            if (!op.Any())
                throw new FrameWorkError()
                {
                    SqlID = this.SqlID,
                    ErrorObject = "Business",
                    ErrorType = "重复参数赋值时",
                    Summary = ParamName + "参数没有定义"
                };
            if (!op.First().Repeated)
                throw new FrameWorkError()
                {
                    SqlID = this.SqlID,
                    ErrorObject = "Business",
                    ErrorType = "重复参数赋值时",
                    Summary = ParamName + "这不是一个重复参数"
                };

            var opfirst = op.First();
            if (opfirst.ParamValue == null)
                opfirst.ParamValue = new ArrayList();
            ArrayList arrParamValue = (ArrayList)opfirst.ParamValue;
            arrParamValue.Add(ParamValue);
        }


    }


}
